import pymysql


# 连接数据库
def connectDB():
    # 连接数据库
    conn = pymysql.connect(host='127.0.0.1',  # 连接名称，默认127.0.0.1
                           user='root',  # 用户名
                           passwd='abc123456',  # 密码
                           port=3306,  # 端口，默认为3306
                           db='easydemo',  # 数据库名称
                           charset='utf8'  # 字符编码
                           )
    return conn


# 添加学生老师或者管理员
def Add(data):
    conn = connectDB()
    # 游标
    cursor = conn.cursor()
    # 默认游标取出来的数据是元组((),)
    # DictCursor对应的数据结构{[]，]，如果用的是fetcheone，那么结果是{}

    # 身份判断
    if data['identity'] == 'student':
        sql = "Insert into student (name,password) value (%(name)s, %(password)s);"
    elif data['identity'] == 'teacher':
        sql = "Insert into teacher (name,password) value (%(name)s, %(password)s);"
    elif data['identity'] == 'admin':
        sql = "Insert into admin (name,password) value (%(name)s, %(password)s);"
    else:
        return

    try:
        # 执行sql语句的传入的参数，参数类型可以是元组、列表、字典
        ret = cursor.execute(sql, data)
        print(ret)
        # 增删改都必须进行提交操作(commit)
        conn.commit()
    except  Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
    finally:
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()


# 登录验证
def login(data):
    conn = connectDB()
    # 游标
    cursor = conn.cursor()

    # 身份判断
    if data['identity'] == 'student':
        sql = "Select id from student where name=%(name)s and password=%(password)s"
    elif data['identity'] == 'teacher':
        sql = "Select id from teacher where name=%(name)s and password=%(password)s"
    elif data['identity'] == 'admin':
        sql = "Select * from admin where name=%(name)s and password=%(password)s"
    else:
        return 0

    # 数据库查找
    try:
        # 执行sql语句的传入的参数，参数类型可以是元组、列表、字典
        cursor.execute(sql, data)
        data = cursor.fetchall()

        if data:
            # 取出对应的id
            print(data[0][0])
            return data[0][0]
        else:
            # 没有在数据库中找到账号
            print('no')
            return 0
    except  Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
    finally:
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()


# 开设课程
def AddCourse(data):
    conn = connectDB()
    cursor = conn.cursor()
    try:
        # 新建课程
        sql = 'Insert into course (name) value (%(name)s);'
        cursor.execute(sql, data)
        # 获取新建课程的id
        data['course_id'] = cursor.lastrowid

        # 建立课程与老师的连接
        sql = 'Insert into teacher_course (teacher_id,course_id) value (%(teacher_id)s, %(course_id)s);'
        ret = cursor.execute(sql, data)
        conn.commit()

        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        return ret
    except  Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
        return 0


# 加入课程（学生）
def JoinCourse(data):
    conn = connectDB()
    cursor = conn.cursor()
    try:
        # 建立课程与学生的连接
        sql = 'Insert into student_course (student_id,course_id) value (%(student_id)s, %(course_id)s);'
        ret = cursor.execute(sql, data)
        conn.commit()

        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        return ret
    except  Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
        return 0


# 布置作业
def AddHomework(data):
    conn = connectDB()
    cursor = conn.cursor()
    try:
        # 新建课程
        sql = 'Insert into homework (name) value (%(name)s);'
        cursor.execute(sql, data)
        # 获取新建作业的id
        data['homework_id'] = cursor.lastrowid

        # 建立作业与课程的连接
        sql = 'Insert into homework_course (homework_id,course_id) value (%(homework_id)s, %(course_id)s);'
        ret = cursor.execute(sql, data)
        conn.commit()

        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        return ret
    except  Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
        return 0


# 获取老师的所有课程
def selectAllCourseByTeacher(teacher_id):
    conn = connectDB()
    cursor = conn.cursor()
    try:
        sql = 'SELECT * FROM teacher LEFT JOIN teacher_course tc on teacher.id = tc.teacher_id LEFT JOIN course c on tc.course_id = c.id where teacher.id=%s'
        cursor.execute(sql, teacher_id)

        courses = cursor.fetchall()
        list_courses = []
        for row in courses:
            result = {'course': row[6], 'course_id': row[5]}
            list_courses.append(result)
        print(list_courses)
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        return list_courses
    except  Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
        return


# 获取学生的所有课程
def selectAllCourseByStudent(student_id):
    conn = connectDB()
    cursor = conn.cursor()
    try:
        sql = 'SELECT * FROM student LEFT JOIN student_course tc on student.id = tc.student_id LEFT JOIN course c on tc.course_id = c.id where student.id=%s'
        cursor.execute(sql, student_id)

        courses = cursor.fetchall()
        list_courses = []
        for row in courses:
            result = {'course': row[6], 'course_id': row[5]}
            list_courses.append(result)
        print(list_courses)
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        return list_courses
    except  Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
        return


# 获取课程的所有作业
def selectAllHomeworkByCourse(course_id):
    conn = connectDB()
    cursor = conn.cursor()
    try:
        sql = 'SELECT * FROM course LEFT JOIN homework_course hc on course.id = hc.course_id LEFT JOIN homework h on hc.homework_id = h.id where course.id=%s;'

        cursor.execute(sql, course_id)
        homeworks = cursor.fetchall()
        print(homeworks)
        list_homeworks = []
        for row in homeworks:
            if row[5] is not None:
                result = {'homework': row[5], 'homework_id': row[4]}
                list_homeworks.append(result)
        print(list_homeworks)
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        return list_homeworks
    except  Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
        return


# 提交作业
def submitHomework(data):
    conn = connectDB()
    cursor = conn.cursor()

    try:
        # 查找有没有之前的作业
        sql = 'select * from student_homework where student_id=%(student_id)s'
        ret = cursor.execute(sql, data)

        if ret:
            # 填作业信息
            sql = 'UPDATE student_homework set name=%(name)s where student_id = %(student_id)s;'
            ret = cursor.execute(sql, data)
            print(ret)
            conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            conn.close()
            return ret
        else:
            # 填作业信息
            sql = 'Insert into student_homework (homework_id, student_id, name) value (%(homework_id)s,%(student_id)s,%(name)s);'

            ret = cursor.execute(sql, data)
            print(ret)
            conn.commit()
            # 关闭游标
            cursor.close()
            # 关闭连接
            conn.close()
            return ret

    except Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
        return 0


# 获取学生作业
def getStudentHW(data):
    conn = connectDB()
    cursor = conn.cursor()

    try:
        # 查找有没有之前的作业
        sql = 'SELECT * FROM student_homework LEFT JOIN homework  on student_homework.homework_id = homework.id LEFT JOIN student  on student_homework.student_id = student.id where homework.id=%(homework_id)s'
        ret = cursor.execute(sql, data)
        if ret:
            studentHWs = cursor.fetchall()
            print(studentHWs)
            list_studentHWs = []
            for row in studentHWs:
                result = {'homework_id': row[0], 'homework_name': row[3], 'score': row[2], 'student_id': row[6],
                          'student_name': row[7]}
                list_studentHWs.append(result)

            print(list_studentHWs)
            return list_studentHWs
        else:
            return 0

    except Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
        return 0


# 上传分数
def submitScore(data):
    conn = connectDB()
    cursor = conn.cursor()

    try:
        sql = 'UPDATE student_homework set score=%(score)s where homework_id = %(homework_id)s and student_id=%(student_id)s;'
        ret = cursor.execute(sql, data)
        print(ret)
        conn.commit()
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        return ret

    except Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
        return 0


# 获取学生信息
def getStudent():
    conn = connectDB()
    cursor = conn.cursor()
    try:
        sql = 'SELECT * FROM student'
        ret = cursor.execute(sql)
        if ret:
            students = cursor.fetchall()
            print(students)
            list_students = []
            for row in students:
                result = {'id': row[0], 'name': row[1]}
                list_students.append(result)
            return list_students
        else:
            return 0

    except Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
        return 0


# 获取学生信息
def getTeacher():
    conn = connectDB()
    cursor = conn.cursor()
    try:
        sql = 'SELECT * FROM teacher'
        ret = cursor.execute(sql)
        if ret:
            teachers = cursor.fetchall()
            print(teachers)
            list_teachers = []
            for row in teachers:
                result = {'id': row[0], 'name': row[1], 'status': row[2]}
                list_teachers.append(result)
            return list_teachers
        else:
            return 0

    except Exception as e:
        print("error")
        # 对插入、修改、删除的数据进行撤销，表示数据回滚（回到没有修改数据之前的状态）
        conn.rollback()
        return 0


# 审核老师通过
def allowTeacher(datas):
    conn = connectDB()
    cursor = conn.cursor()

    try:
        for data in datas:
            sql = 'select status from teacher where id = %(id)s;'
            cursor.execute(sql, data)
            status = cursor.fetchall()
            print(status)
            if status[0][0] == 0:
                sql = 'UPDATE teacher set status=1 where id = %(id)s;'
                ret = cursor.execute(sql, data)
                print(ret)

        conn.commit()
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        return 1
    except Exception as e:
        conn.rollback()
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        return 0


if __name__ == '__main__':
    allowTeacher([{'id': 1, 'name': "曾国铭", 'status': 0}, {'id': 2, 'name': "lx", 'status': 0}])
